<?php

namespace app\admin\controller\excels;


use app\admin\controller\BaseController;
use longzy\helper;
use think\exception\ValidateException;
use think\facade\Db;
use think\facade\Lang;
use think\facade\View;

class Excels extends BaseController {
    protected $model;
    protected $sign='MyNameIsNiuBiClass';
    /**
     * 初始化操作
     */
    public function initialize() {
        parent::initialize();
        $this->model = new \app\admin\model\cms\Archives;
    }

    public function index($action = '') {
        if ($action == 1) {
            //页面树形菜单
            $res = $this->getCateTree();
            return json($res);
        } elseif ($action == 2) {
            //页面表格数据
            $res = $this->getTableList();
            return json($res);
            echo $res;
        } else {
            return $this->view->fetch();
        }
    }


    /**
     * 获取栏目的jstree
     */
    public function getCateTree() {
        if ($this->request->isAjax()) {
            $list = Db::view('category', 'id,model_id,parent_id,type,name,title as text')
                ->view('model', 'controller', 'category.model_id=model.id')
                ->where(['category.status' => 'normal', 'category.lang' => Lang::getLangSet()])
                ->select()->toArray();
            foreach ($list as $k => $v) {
                $list[$k]['state'] = array(
                    'opened' => true,
                    'disabled' => false,
                    'selected' => false
                );
                if ($v['type'] == 'link') {
                    $list[$k]['icon'] = 'fas fa-file';
                } else {
                    $list[$k]['icon'] = 'fas fa-list';
                }

                $list[$k]['controller'] = strtolower($v['controller']) != 'guestbook'?1:0;


            }
            $list = helper::list_to_tree($list, 'id', 'parent_id', 'children');
            $list = array_values($list);
            return $list;
        }
    }


    /**
     * 获取栏目的jstree
     */
    public function getTableList() {
        if ($this->request->isAjax()) {
            // 获取表名
            $model_id = $this->request->param('model_id');
            $category_id = $this->request->param('category_id');
            // 是否显示下级
            $issub = $this->request->param('issub', '');
            $cateMap = [['category_id', '=', $category_id]];
            if ($issub == 1) {
                $cids = Db::name('category')->where(['parent_id' => $category_id, 'model_id' => $model_id])->cache(3600)->column('id');
                if (!empty($cids)) { // 上级显示下级的菜单
                    $cids[] = $category_id;
                    $cateMap = [['category_id', 'in', $cids]];
                }
            }

            list($map, $limit, $offset, $order, $sort) = $this->buildparams(null, 'a');

            $data = $this->model
                ->alias('a')
                ->leftJoin('admin', 'a.admin_id=admin.id')
                ->leftJoin('category category', 'a.category_id=category.id')
                ->where($map)
                ->where($cateMap)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->field('a.*,admin.username,category.title as category_title')
                ->select();

            $total = $this->model
                ->alias('a')
                ->leftJoin('admin', 'a.admin_id=admin.id')
                ->leftJoin('category category', 'a.category_id=category.id')
                ->where($map)
                ->where($cateMap)
                ->order($sort, $order)
                ->count();
            $data->append(['publish_time_text'])->toArray();
            return ['total' => $total, 'rows' => $data];
        }
    }



    //文档导出Excel内容
    public function export($ids = '', $model_id = 0, $category_id = 0) {
        $res = $this->_getData($ids, $model_id, $category_id);
        if ($res['code'] != 200) {
            return $res;
        }

        $category = Db::name('category')->where('id', $category_id)->find();


        $cellNum = $res['field_title'];
        $format = 'xlsx'; //导出格式，只允许 Xls 和 Xlsx ，注意大写开头
        //默认文件名
        $fileName = $category['title'] . '-' . date('YmdHis');
        $savePath = app()->getRootPath() . 'public/excel';
        //默认开启第一个工作表
        $objExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $objExcel->getActiveSheet();


        $rowNumber = 1; //初始行号
        $excelKey = 'C';//初始列号
        //设置表头
        $sheet->setCellValue('A1', '序号');
        $sheet->setCellValue('B1', 'ID');
        foreach ($cellNum as $k => $v) {
            $sheet->setCellValue($excelKey . $rowNumber, $v);
            $excelKey++;
        }

        //内容输入
        $rowNumber = 2;
        foreach ($res['data'] as $k => $v) {
            $excelKey = 'C';
            $sheet->setCellValue("A{$rowNumber}", $k+1);
            $sheet->setCellValue("B{$rowNumber}", $v['id']);
            foreach ($res['field_name'] as $val) {
                $sheet->setCellValue($excelKey . $rowNumber, htmlspecialchars_decode($v[$val]));
                $excelKey++;
            }
            $rowNumber++;
        }

        //Set column widths 设置列宽度
        $objExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(50);//所有单元格（列）默认宽度
        $objExcel->getActiveSheet()->getColumnDimension('A')->setWidth('10');
        $objExcel->getActiveSheet()->getColumnDimension('B')->setWidth('10');


        $excelFileName = $fileName . '.' . $format;
        if (!file_exists($savePath)) {
            mkdir($savePath, 0777, true);
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($objExcel);
        $writer->save($savePath . '/' . $excelFileName);
        $res = returnMsg('200', 'Excel文件保存成功', ['url' => '/excel/' . $excelFileName]);
        return $res;
    }

    //文档导出附件
    public function exportFile($ids = '', $model_id = 0, $category_id = 0) {
        $res = $this->_getData($ids, $model_id, $category_id, true);
        if ($res['code'] != 200) {
            return $res;
        }

        $list = $res['data'];

        $path = app()->getRootPath() . 'public';
        if (!file_exists($path . '/zip')) {
            mkdir($path . '/zip', 0777, true);
        }

        ob_clean();

        $zipName = '/zip/' . uniqid() . '.zip';  //zip名
        $zipPath = $path . $zipName;

        $overwrite = false;                        //覆盖重写
        $zip = new \ZipArchive();
        if ($zip->open($zipPath, $overwrite ? \ZIPARCHIVE::OVERWRITE : \ZIPARCHIVE::CREATE) === true) {
            foreach ($list as $k => $v) {
                if ($v['thumb'] && file_exists($path . $v['thumb'])) {
                    $ext = helper::file_extension($v['thumb']);
                    $zip->addFile($path . $v['thumb'], $v['title'] . "/封面图.{$ext}");
                } else {

                }

                if ($v['annex'] && file_exists($path . $v['annex'])) {
                    $ext = helper::file_extension($v['annex']);
                    $zip->addFile($path . $v['annex'], $v['title'] . "/附件.{$ext}");
                } else {

                }

                if ($v['video'] && file_exists($path . $v['video'])) {
                    $ext = helper::file_extension($v['video']);
                    $zip->addFile($path . $v['video'], $v['title'] . "/视频.{$ext}");
                } else {

                }

            }

            $zip->close();
            if (file_exists($zipPath)) {
                //下载ZIP
                $res = returnMsg(200, '', ['url' => $zipName]);
            } else {
                $res = returnMsg(500, '文件压缩失败！');
            }
        } else {
            $res = returnMsg(500, '创建压缩包失败！');
        }


        return json($res);
    }

    //导出栏目excel模板
    public function exportDome() {
        $category_id = $this->request->param('category_id');
        if (!$category_id) {
            $res = returnMsg(400, '请选择栏目');
            return json($res);
        }
        $model_id = $this->request->param('model_id');
        if (!$model_id) {
            $res = returnMsg(400, '请选择栏目');
            return json($res);
        }
        $data = getCateForm($category_id, $model_id);
        $data = $data['data'];
        $category = get_category($category_id);


        $list = [];
        $list['field_title'][] = '序号';
        $list['field_name'][] = '序号';
        foreach ($data as $k => $v) {
            if (in_array($v['form_type'], ['text', 'textarea', 'editor']) && $v['field_group'] == '常规') {
                $list['field_title'][] = $v['field_title'];
                $list['field_name'][] = $v['field_name'];
            } elseif (in_array($v['form_type'], ['downfile', 'image']) && $v['field_group'] == '常规') {
                $list['field_title'][] = $v['field_title'] . '（文件URL或者留空）';
                $list['field_name'][] = $v['field_name'];
            } elseif ($v['form_type'] == 'radio' && $v['field_group'] == '常规') {
                $tis = '【可选值=';
                foreach ($v['data_list'] as $key => $val) {
                    $tis .= "$key($val)、";
                }
                $tis .= '】';

                $list['field_title'][] = $v['field_title'] . $tis;
                $list['field_name'][] = $v['field_name'];
            }
        }


        $cellNum = $list['field_title'];
        $format = 'xlsx'; //导出格式，只允许 Xls 和 Xlsx ，注意大写开头
        //默认文件名
        $fileName = $category['title'] . '-Excel导入模板';
        $savePath = app()->getRootPath() . 'public/excel';

        //清除缓冲区，某些PHP版本会影响下载的文件无法打开
        ob_end_clean();
        //默认开启第一个工作表
        $objExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $objExcel->getActiveSheet();

        $rowNumber = 1; //初始行号
        $excelKey = 'A';//初始列号
        //设置表头
        foreach ($cellNum as $k => $v) {
            $sheet->setCellValue($excelKey . $rowNumber, $v);
            $excelKey++;
        }


        //Set column widths 设置列宽度
        $objExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(50);//所有单元格（行）默认高度
        $objExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(50);//所有单元格（列）默认宽度
        $objExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);//设置行高度
        $objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);//设置列宽度


        //设置第二个工作表
        $fileKey = implode(',', $list['field_title']) . ',' . implode(',', $list['field_name']);
        $fileKey = $fileKey . $category_id . $model_id . $this->sign;
        $fileKey = strtoupper(md5($fileKey));

        $objExcel->createSheet();
        $objExcel->setactivesheetindex(1);
        $sheet = $objExcel->getActiveSheet(1);
        $sheet->setTitle('系统数据');


        //设置表头
        $sheet->setCellValue('A1', '这里的数据打死都不能修改！！！！重要的事情说三遍');
        $sheet->setCellValue('A2', '这里的数据打死都不能修改！！！！重要的事情说三遍');
        $sheet->setCellValue('A3', '这里的数据打死都不能修改！！！！重要的事情说三遍');
        $rowNumber = 4; //初始行号
        $excelKey = 'A';//初始列号
        foreach ($list['field_name'] as $k => $v) {
            $sheet->setCellValue($excelKey . $rowNumber, $v);
            $excelKey++;
        }
        $sheet->setCellValue('A5', $fileKey);
        $sheet->setCellValue('A6', $category_id);
        $sheet->setCellValue('A7', $model_id);


        //默认显示第一个工作报表
        $objExcel->setactivesheetindex(0);


        $excelFileName = $fileName . '.' . $format;
        if (!file_exists($savePath)) {
            mkdir($savePath, 0777, true);
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($objExcel);
        $writer->save($savePath . '/' . $excelFileName);
        $res = returnMsg('200', 'Excel文件保存成功', ['url' => '/excel/' . $excelFileName]);
        return $res;
    }

    //导入模板数据
    public function import() {
        $res = $this->_getExcelData();
        if ($res['code'] != '200') {
            $this->error($res['msg']);
        }

        $lang = Lang::getLangSet();
        $model_id = $res['model_id'];
        $category_id = $res['category_id'];
        //查询修字段是主表还是附表
        $fieldKey = Db::name('model_field')->field('field_name,field_title,iscore')->where('model_id', $res['model_id'])->select()->toArray();
        $fieldKeySeting = [];
        foreach ($fieldKey as $k => $v) {
            $fieldKeySeting[$v['field_name']] = $v['iscore'];
        }

        //excel数据处理
        $list = [];
        foreach ($res['data'] as $k => $v) {
            //去除第一行
            if ($k) {
                $a = [];
                $b = [];
                foreach ($v as $key => $val) {
                    //去除第一列
                    $tableNameKey = $res['keys'][$key];
                    if ($key && $fieldKeySeting[$tableNameKey] == 1) {
                        //主表数据
                        $a[$res['keys'][$key]] = $val;
                    } elseif ($key && $fieldKeySeting[$tableNameKey] == 0) {
                        //附表数据
                        $b[$res['keys'][$key]] = $val;
                    }
                }
                $list[] = ['archives' => $a, 'model' => $b];
            }

        }

        //数据验证
        foreach ($list as $k => $v) {
            $num = $k + 2;
            $v['archives']['category_id'] = $category_id;
            $v['archives']['model_id'] = $model_id;
            $v['archives']['lang'] = $lang;
            try {
                validate(\addons\excels\validate\Category::class)->check($v['archives']);
            } catch (ValidateException $e) {
                // 验证失败 输出错误信息
                $this->error("Excel第{$num}行：" . $e->getMessage());
            }
        }

        //模型名称
        $tableName = Db::name('model')->where('id', $model_id)->value('tablename');
        $category = Db::name('category')->where('id', $category_id)->find();
        Db::startTrans();
        try {
            $time = time();
            foreach ($list as $k => $v) {
                $v['archives']['category_id'] = $category_id;
                $v['archives']['model_id'] = $model_id;
                $v['archives']['lang'] = $lang;
                $v['archives']['show_tpl'] = $category['show_tpl'];
                $v['archives']['publish_time'] = $time;
                $v['archives']['update_time'] = $time;
                $v['archives']['create_time'] = $time;

                $id = DB::name('archives')->insertGetId($v['archives']);
                $v['model']['id'] = $id;
                DB::name($tableName)->insert($v['model']);
            }
            Db::commit();
        } catch (\Exception $e) {
            // 回滚事务
            Db::rollback();
            $this->error($e->getMessage());
        }
        $this->success('共导入' . count($list) . '条数据');
    }

    //获取上传的Excel内容
    protected function _getExcelData() {
        try {
            $file = $_FILES['file'];
        } catch (\Exception $e) {
            $res = returnMsg('400', '请选择Excel文件', ['error' => $e->getMessage()]);
            return $res;
        }

        try {
            //获取表格的大小，限制上传表格的大小
            //                $fileSize = $file['size'];
            //                if ($fileSize > 10 * 1024 * 1024) {
            //                    $res = returnMsg('400', "文件大小不能超过 10 M");
            //                    return $res;
            //                }

            //文件格式要求
            $fileExtendName = strtolower(substr(strrchr($file['name'], '.'), 1));
            if (!in_array($fileExtendName, ['xls', 'xlsx'])) {
                $res = returnMsg('400', '仅支持 xls 和 xlsx 格式文件');
                return $res;
            }
            $fileMime = mime_content_type($file['tmp_name']);
            if (!in_array($fileMime, ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'])) {
                $res = returnMsg('400', '仅支持 xls 和 xlsx 格式文件');
                return $res;
            }

            if (is_uploaded_file($file['tmp_name'])) {
                // 有Xls和Xlsx格式两种
                $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(ucfirst($fileExtendName));

                $filename = $file['tmp_name'];
                $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
                $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                $highestRow = $sheet->getHighestRow();       // 取得总行数
                $highestColumn = strtoupper($sheet->getHighestColumn());   // 取得总列数


                //excel导出为EXcel
                $excelData = [];
                $key = 'A';
                while ($highestColumn != $key) {
                    $colnum[] = $key;
                    //防止死循环，要是出现个10000列的EXCEL????去死吧
                    if (count($colnum) >= 10000) {
                        break;
                    }
                    ++$key;
                }
                $colnum[] = $highestColumn;
                for ($i = 1; $i <= $highestRow; $i++) {
                    foreach ($colnum as $v) {
                        $excelData[$i - 1][] = (string)trim($objPHPExcel->getActiveSheet()->getCell($v . $i)->getValue());
                    }
                }

                //excel中的第二张sheet
                $keys = [];
                $objPHPExcel->setActiveSheetIndex(1);


                foreach ($colnum as $v) {
                    $keys[] = (string)trim($objPHPExcel->getActiveSheet()->getCell("{$v}4")->getValue());
                }

                //文件key，确认文件关键信息是否被篡改
                $excelFileKey = $objPHPExcel->getActiveSheet()->getCell('A5')->getValue();
                $category_id = $objPHPExcel->getActiveSheet()->getCell('A6')->getValue();
                $model_id = $objPHPExcel->getActiveSheet()->getCell('A7')->getValue();
                $excelTitle = $excelData[0];


                $fileKey = implode(',', $excelTitle) . ',' . implode(',', $keys);
                $fileKey = $fileKey . $category_id . $model_id . $this->sign;
                $fileKey = strtoupper(md5($fileKey));

                //关键数据被篡改
                if ($excelFileKey != $fileKey) {
                    $res = returnMsg('400', 'Excel模板验证失败，请重新下载上传模板');
                    return $res;
                }


                $res = returnMsg('200', '读取成功', $excelData, ['keys' => $keys, 'model_id' => $model_id, 'category_id' => $category_id]);
            } else {
                $res = returnMsg('400', '文件非HPPT POST上传！');
            }
        } catch (\Exception $e) {
            $res = returnMsg('500', '系统错误', ['error' => $e->getMessage()]);
        }

        return $res;

    }

    /**
     * 获取数据
     * @param string $ids 用英文, 分开
     * @param int $model_id
     * @param int $category_id
     * @return array|string
     */
    private function _getData($ids = '', $model_id = 0, $category_id = 0, $file = false) {
        try {
            //模型名称
            $tableName = Db::name('model')->where('id', $model_id)->value('tablename');
            $formFieldKey = getCateForm($category_id, $model_id);


            $list = $this->model::view('archives')
                ->view($tableName, '*', "{$tableName}.id=archives.id")
                ->where('archives.id', 'in', $ids)
                ->select()->toArray();
            $data = [];
            foreach ($list as $k => $v) {
                $a = [];
                $a['id'] = isset($v['id'])?$v['id']:'';
                foreach ($formFieldKey['data'] as $key => $val) {
                    $tableKey = $val['field_name'];
                    $type = $val['form_type'];
                    if ($file) {
                        //只获取文件类数据
                        if (in_array($type, ['downfile', 'image']) || $tableKey = 'title') {
                            $a[$tableKey] = $v[$tableKey];
                        }
                    } else {
                        if ($type == 'radio') {
                            //单选的
                            $b = $val['data_list'];
                            $a[$tableKey] = isset($b[$v[$tableKey]]) ? $b[$v[$tableKey]] : '';
                        } elseif ($type == 'selectpage') {
                            //动态下拉
                            $b = $val['data_list'];
                            $c = Db::name($b['table'])->field($b['show-field'])->where($b['key-field'], 'in', $v[$tableKey])->select()->toArray();
                            $a[$tableKey] = implode(',', array_column($c, $b['show-field']));
                        } else {
                            $a[$tableKey] = $v[$tableKey];
                        }
                    }
                }
                $data[] = $a;
            }
            $formFieldKey['field'] = $formFieldKey['data'];
            unset($formFieldKey['data']);
            $res = returnMsg('200', 'OK', $data, $formFieldKey);
        } catch (\Exception $e) {
            $res = returnMsg('400', $e->getMessage());

        }
        return $res;
    }

}